﻿using Mysoft.QuickCode.NPOI.HSSF.UserModel;
using Mysoft.QuickCode.NPOI.SS.UserModel;
using Mysoft.QuickCode.NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace Mysoft.QuickCode.Office.Excel
{
    /// <summary>
    /// 工作表工具
    /// </summary>
    public class SheetTool
    {
        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="file">Excel文件路径</param>
        /// <param name="index">工作表Sheet在Excel中的页签索引，从0开始</param>
        public SheetTool(string file, int index)
        {
            FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = null;
            if (file.EndsWith(".xls", StringComparison.OrdinalIgnoreCase))
            {
                workbook = new HSSFWorkbook(fileStream);
            }
            else
            {
                workbook = new XSSFWorkbook(fileStream);
            }
            this.Sheet = workbook.GetSheetAt(index);

            //默认的起始结束行
            this.DataRowBeginIndex = this.Sheet.FirstRowNum;
            this.DataRowEndIndex = this.Sheet.LastRowNum;

            this.IgnoreEmptyRow = true;  //默认忽略空数据行
            this.ErrorMessages = new List<string>();    //数据校验结果
        }
        /// <summary>
        /// NPOI工作表对象接口
        /// </summary>
        public ISheet Sheet { private set; get; }
        /// <summary>
        /// 读取的数据起始行（从0开始）
        /// </summary>
        public int DataRowBeginIndex { set; get; }
        /// <summary>
        /// 读取的数据结束行（从0开始）
        /// </summary>
        public int DataRowEndIndex { set; get; }

        /// <summary>
        /// 详细列头行索引
        /// </summary>
        public int DetailHeaderRowIndex { set; get; }

        ///// <summary>
        ///// 读取数据的起始列（从0开始）
        ///// </summary>
        //public int DataColumnBeginIndex { set; get; }

        ///// <summary>
        ///// 读取数据的结束列（从0开始）
        ///// </summary>
        //public int DataColumnEndIndex { set; get; }

        /// <summary>
        /// 忽略空数据行？
        /// </summary>
        public bool IgnoreEmptyRow { set; get; }

        /// <summary>
        /// 列信息
        /// </summary>
        public SheetColumnTool[] ColumnTools { set; get; }

        private List<string> ErrorMessages { set; get; }

        /// <summary>
        /// 获取数据校验返回结果
        /// </summary>
        /// <returns></returns>
        public List<string> GetErrorMessages()
        {
            return this.ErrorMessages;
        }

        /// <summary>
        /// Excel数据转换为DataTable对象，生成后如果需要知道是否存在错误信息，请调用GetErrorMessages方法获取错误信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public DataTable GetTable()
        {
            //公式计算器
            IFormulaEvaluator formulaEval = null;
            if (Sheet.Workbook is HSSFWorkbook)
            {
                formulaEval = new HSSFFormulaEvaluator(Sheet.Workbook);
            }
            else
            {
                formulaEval = new XSSFFormulaEvaluator(Sheet.Workbook);
            }

            //创建DataTable
            DataTable table = new DataTable();
            if (ColumnTools == null)
            {
                //没有列信息的时候默认第一行解析为列信息
                DataRowBeginIndex = DetailHeaderRowIndex + 1;  //数据起始行
                //初始化默认列
                IRow row0 = Sheet.GetRow(DetailHeaderRowIndex);
                ColumnTools = new SheetColumnTool[row0.PhysicalNumberOfCells];   //初始化列数
                for (int i = 0; i < row0.PhysicalNumberOfCells; i++)
                {
                    ColumnTools[i] = new SheetColumnTool()
                    {
                        ColumnIndex = i,
                        ColumnName = row0.GetCell(i).ToString()
                    };  //默认列配置信息
                }
            }

            foreach (SheetColumnTool item in ColumnTools)
            {
                //按列名初始化表格
                table.Columns.Add(item.ColumnName);
            }

            //索引都是从0开始
            for (int i = DataRowBeginIndex; i <= DataRowEndIndex; i++)
            {
                IRow row = Sheet.GetRow(i);
                //判断是否空行？
                bool empty = false;

                //非空行，则处理数据
                if (empty == false)
                {
                    DataRow newRow = table.NewRow();
                    int newColIndex = 0;
                    foreach (SheetColumnTool cellTool in ColumnTools)
                    {
                        object value = string.Empty;
                        if (row.GetCell(cellTool.ColumnIndex) == null)
                        {
                            value = string.Empty;
                        }
                        else
                        {
                            if (row.GetCell(cellTool.ColumnIndex).CellType == CellType.Formula)
                            {
                                //有计算公式，读取数据
                                try
                                {
                                    if (DateUtil.IsCellDateFormatted(row.GetCell(cellTool.ColumnIndex)))
                                    {
                                        //日期格式当成纯数字存储
                                        value = row.GetCell(cellTool.ColumnIndex).NumericCellValue;
                                    }
                                    else
                                    {
                                        value = row.GetCell(cellTool.ColumnIndex).NumericCellValue;
                                        //value = formulaEval.EvaluateInCell(row.GetCell(cellTool.ColumnIndex)).NumericCellValue;
                                    }
                                    //value = row.GetCell(cellTool.ColumnIndex).NumericCellValue;
                                }
                                catch
                                {
                                    value = row.GetCell(cellTool.ColumnIndex).StringCellValue.Trim();
                                }
                            }
                            else if (row.GetCell(cellTool.ColumnIndex).CellType == CellType.Numeric)
                            {
                                //数字字段优先读取数字类型
                                try
                                {
                                    value = row.GetCell(cellTool.ColumnIndex).NumericCellValue;
                                }
                                catch
                                {
                                    //无计算公式，直接读取字符串
                                    value = row.GetCell(cellTool.ColumnIndex).ToString().Trim();
                                }
                            }
                            else
                            {
                                //无计算公式，直接读取字符串
                                value = row.GetCell(cellTool.ColumnIndex).ToString().Trim();
                            }
                        }

                        //存在数据校验器
                        if (cellTool.Validator != null)
                        {
                            //数据校验
                            string result = cellTool.Validator(row, cellTool.ColumnIndex, value.ToString());
                            if (string.IsNullOrEmpty(result) == false)
                            {
                                this.ErrorMessages.Add(result);
                            }
                        }

                        //设置新数据行的列的值
                        newRow[newColIndex++] = value;

                    }
                    table.Rows.Add(newRow);
                }
            }

            return table;
        }

    }
}
